<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>使用公式</title>
  <script src="./js/jexcel.js"></script>
  <script src="./js/jsuites.js"></script>
  <link rel="stylesheet" href="./css/jexcel.css" type="text/css" />
  <link rel="stylesheet" href="./css/jsuites.css" type="text/css" />
</head>

<body>
  <h1>使用公式</h1>
  <p>双击表头触发列排序</p>

  <h3>1、简单的电子表格式公式使用</h3>
  <div id="spreadsheet"></div>

  <h3>2、创建自定义公式</h3>
  <div id="spreadsheet2"></div>
</body>
<script type="module">
  import { text } from "./js/language.js";

  // 1、简单的电子表格式公式使用
  var data = [
    ['Crayons Crayola only (No Rose Art)', 2, 5.01, '=B1*C1'],
    ['Colored Pencils Crayola only', 2, 4.41, '=B2*C2'],
    ['Expo Dry-erase Markers Wide', 4, 3.00, '=B3*C3'],
    ['Index Cards Unlined', 3, 6.00, '=B4*C4'],
    ['Tissues', 10, '1.90', '=B5*C5'],
    ['Ziploc Sandwich-size Bags', 5, 1.00, '=B6*C6'],
    ['Thin Markers Crayola only', 2, 3.00, '=B7*C7'],
    ['Highlighter', 4, 1.20, '=B8*C8'],
    ['Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '=SUM(D1:D8)'],
  ];

  jspreadsheet(document.getElementById('spreadsheet'), {
    data: data,
    columns: [
      { type: 'text', title: 'Product', width: '300' },
      { type: 'text', title: 'Qtd', width: '80' },
      { type: 'text', title: 'Price', width: '100', mask: '#.##,00', decimal: '.' },
      { type: 'text', title: 'Total', width: '100' },
    ],
    updateTable: function (instance, cell, col, row, val, label, cellName) {
      if (cell.innerHTML == 'Total') {
        cell.parentNode.style.backgroundColor = '#fffaa3';
      }

      if (col == 3) {
        if (parseFloat(label) > 10) {
          cell.style.color = 'red';
        } else {
          cell.style.color = 'green';
        }
      }
    },
    // 列排序，双击表头触发
    columnSorting: true,
    text
  });

  // 2 创建自定义公式
  var COLORIZE = function (v) {
    v = '<span style="color:' + v + '">' + v.toUpperCase() + '</span>';
    return v;
  }

  var data1 = [
    ['red', '=COLORIZE(A1)'],
    ['green', '=COLORIZE(A2)'],
    ['blue', '=COLORIZE(A3)'],
  ];

  jspreadsheet(document.getElementById('spreadsheet2'), {
    data: data1,
    stripHTML: false,
    columns: [
      { type: 'text', width: '300' },
      { type: 'text', width: '200' },
    ]
  });
</script>

</html>